const express = require('express');
const router = express.Router();
const db = require('../../../MySql');
const xlsx = require('xlsx');

router.post('/qu/qu/export', async (req, res) => {
    try {
        // 查询数据
        const [rows] = await db.query(`SELECT 
                q.id,
                q.qu_type,						
                q.image as qu_img,
                q.content,
                q.analysis,
				r.repo_id,
                a.is_right,
                a.image as qu_answer_img,
                a.content as qu_answer_content,
                a.analysis as qu_answer_analysis
            FROM el_qu q
            LEFT JOIN el_qu_answer a ON q.id = a.qu_id
            LEFT JOIN el_qu_repo r ON q.id = r.qu_id`);

        // 构造表头和字段映射
        //题目序号 题目类型 题目内容	整体解析	题目图片	题目视频	所属题库	是否正确项	选项内容	选项解析	选项图片



        const headers = [
            '题目序号', '题目类型', '题目内容', '整体解析', '题目图片', '所属题库',
            '是否正确项', '选项内容', '选项解析', '选项图片'
        ]
        const fields = [
            'id', 'qu_type', 'content', 'analysis', 'qu_img', 'repo_id',
            'is_right', 'qu_answer_content', 'qu_answer_analysis', 'qu_answer_img'
        ]

        // 处理数据，每道题第一行显示题目内容，后续只显示选项
        let lastQuId = null;
        let questionNumber = 0; // 题目序号计数器，从0开始
        const data = rows.map((row, index) => {
            if (row.id !== lastQuId) {
                lastQuId = row.id;
                questionNumber++; // 遇到新题目时序号递增
                const rowData = fields.map(f => row[f]);
                rowData[0] = questionNumber; // 将题目ID替换为题目序号
                return rowData;
            } else {
                // 后续行题目相关字段置空，只显示选项相关字段
                return [
                    questionNumber, row.qu_type, '', '', '', '', // 题目相关字段为空，但序号保持一致
                    row.is_right, row.qu_answer_content, row.qu_answer_analysis, row.qu_answer_img // 选项相关字段
                ]
            }
        })

        // 生成 sheet
        const titleRow = ['题库导出数据']; // 标题行，可以根据需要修改标题内容
        const worksheet = xlsx.utils.aoa_to_sheet([titleRow, headers, ...data]);
        const workbook = xlsx.utils.book_new();
        xlsx.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
        const buffer = xlsx.write(workbook, { type: 'buffer', bookType: 'xlsx' })

        // 发送文件流
        res.send(buffer)
        // return res.json({
        //     "data": data
        // })
    } catch (error) {
        console.error('导出题库失败:', error);
        res.status(500).json({ message: '导出题库失败' });
    }
});

module.exports = router;